﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using UniversalDataAccess.BusinessEntities;

namespace UniversalDataAccess
{
    public class PersonsDataAccess: DataAccessBase
    {
        public PersonsDataAccess(string dbName, string conn, bool usePredefinedMetadata)
            :base(dbName, conn, usePredefinedMetadata)
        {
        }

        public List<Person> SelectPersonsList()
        {
            List<TObject> tobjList = SelectTObjectsList();

            List<PERSON> persList = dataContext.PERSON.ToList();

            //Merge result
            var res = (from tobj in tobjList
                       join pers in persList on tobj.ID equals pers.ID
                       select new Person
                       {
                           DataBaseName = dataBaseName,
                           CLASS_ID = tobj.CLASS_ID,
                           EXT_ID = tobj.EXT_ID,
                           ID = tobj.ID,
                           NAME = tobj.NAME,
                           PARENT_ID = tobj.PARENT_ID,
                           FIRST_NAME = pers.FIRST_NAME,
                           LAST_NAME = pers.LAST_NAME,
                           ADDRESS = pers.ADDRESS,
                           BIRTH_DATE =  (DateTime)pers.BIRTH_DATE
                       }).ToList();

            return res;
        }

        public Person SelectPersonBy_Id(int Id)
        {
            try
            {
                TObject basePart = SelectTObject_By_Id(Id);

                List<PERSON> persPart = dataContext.PERSON.ToList();

                //Merge result
                var res = (from pers in persPart
                           where pers.ID == basePart.ID
                           select new Person
                           {
                               DataBaseName = dataBaseName,
                               CLASS_ID = basePart.CLASS_ID,
                               EXT_ID = basePart.EXT_ID,
                               ID = basePart.ID,
                               NAME = basePart.NAME,
                               PARENT_ID = basePart.PARENT_ID,
                               FIRST_NAME = pers.FIRST_NAME,
                               LAST_NAME = pers.LAST_NAME,
                               ADDRESS = pers.ADDRESS,
                               BIRTH_DATE = (DateTime)pers.BIRTH_DATE
                           }).FirstOrDefault();

                return res;
            }
            catch
            {
                throw new Exception("Error while SelectPersonBy_Id");
            }
        }

        public void InsertPerson(Person pers, ref int generated_Id)
        {
            try
            {
                //Insert the TObject part
                InsertTObject(pers, ref generated_Id);

                int new_Id = generated_Id;
                PERSON persPart = new PERSON();
                persPart.TOBJECT = dataContext.TOBJECT.Where(t => t.ID == new_Id).FirstOrDefault();
                persPart.ID = new_Id;
                persPart.FIRST_NAME = pers.FIRST_NAME;
                persPart.LAST_NAME = pers.LAST_NAME;
                persPart.ADDRESS = pers.ADDRESS;
                persPart.BIRTH_DATE = pers.BIRTH_DATE;

                //Submit changes
                dataContext.AddToPERSON(persPart);
                dataContext.SaveChanges();
            }
            catch (Exception ex)
            {
                throw new Exception("Error while InsertPerson");
            }
        }

        public void UpdatePerson(Person pers)
        {
            try
            {
                //Update the TObject part
                UpdateTObject(pers);

                PERSON persPart = dataContext.PERSON.Where(t => t.ID == pers.ID).FirstOrDefault();

                persPart.FIRST_NAME = pers.FIRST_NAME;
                persPart.LAST_NAME = pers.LAST_NAME;
                persPart.ADDRESS = pers.ADDRESS;
                persPart.BIRTH_DATE = pers.BIRTH_DATE;

                //Submit changes
                dataContext.SaveChanges();
            }
            catch (Exception ex)
            {
                throw new Exception("Error while UpdatePerson");
            }
        }

        public void DeletePerson(Person pers)
        {
            try
            {
                PERSON persPart = dataContext.PERSON.Where(t => t.ID == pers.ID).FirstOrDefault();

                dataContext.DeleteObject(persPart);
                dataContext.SaveChanges();


                //Delete the TObject part
                DeleteTObject(pers);
            }
            catch (Exception ex)
            {
                throw new Exception("Error while DeletePerson");
            }
        }
       
    }
}
